import os
import random

from dotenv import load_dotenv

from ..models.textbook_model import Textbook
from sqlalchemy.orm import Session
from sqlalchemy import select, update
from fastapi import HTTPException
from sqlalchemy import desc
from ..utils.photo_tools import get_image_as_base64
from ..utils.uploads_oss import main as upload_on_oss

load_dotenv()
SOURCES_PATH = os.getenv("SOURCES_PATH")

def get_all_textbooks(db: Session):
    query = select(Textbook)
    textbooks = db.execute(query).scalars().all()
    return[textbook.as_dict() for textbook in textbooks]

def create_textbook(db: Session, name, path,type,subject, grade,cover,starts, oss_url=None):
    if db.execute(select(Textbook).where(Textbook.name ==name)).scalars().first():
        raise HTTPException(status_code=400, detail=f"Textbook {name} already exists")
    file_path = os.path.join(SOURCES_PATH, path)
    upload_on_oss(path,file_path)
    new_textbook=Textbook(name=name, path=path,type=type,subject=subject,
                          grade=grade,cover=cover,starts=starts,
                          oss_url="http://whatteam1.oss-cn-beijing.aliyuncs.com/"+path)
    db.add(new_textbook)
    db.commit()
    db.refresh(new_textbook)
    return new_textbook.as_dict()


def update_textbook(db: Session, textbook_id, textbook_data):
    exist_textbook = db.execute(select(Textbook).where(Textbook.id == textbook_id)).scalar()
    if not exist_textbook:
        raise HTTPException(status_code=404, detail=f"Textbook {textbook_id} not found")
    for k, v in textbook_data.model_dump().items():
        setattr(exist_textbook, k, v)
    db.commit()
    return exist_textbook


def delete_textbook(db: Session, textbook_id):
    exist_textbook = db.get(Textbook, textbook_id)
    if not exist_textbook:
        raise HTTPException(status_code=404, detail="Textbook not found")
    db.delete(exist_textbook)
    db.commit()
    return exist_textbook.as_dict()


def check_textbook_exists(db: Session, textbook_id):
    return db.get(Textbook, textbook_id) is not None


def get_textbook_page(db:Session,subject: str, page: int = 1, page_size: int = 10):
    try:
        offset = (page - 1) * page_size
        textbook_list = (
            db.query(Textbook)
            .filter(Textbook.subject == subject)
            .order_by(desc(Textbook.starts))  # 按 starts 字段降序排序
            .offset(offset)
            .limit(page_size)
            .all()
        )
        return textbook_list
    finally:
        db.close()
def get_textbook_by_grade_subject(db: Session, grade: str, subject: str, page: int = 1, page_size: int = 10):

    try:
        offset = (page - 1) * page_size

        # 构建查询条件
        query = (
            db.query(Textbook)
            .filter(Textbook.grade == grade)
            .filter(Textbook.subject == subject)

            .offset(offset)
            .limit(page_size)
        )

        total_count = query.count()
        total_pages = (total_count + page_size - 1) // page_size  # 向上取整

        results = []
        for textbook in query.all():
            data = textbook.as_dict()
            if "副本" in data.get("path") :
                continue
            # if data.get("cover"):
            #     cover_base64 = get_image_as_base64(data["cover"])
            #     data["cover"] = cover_base64 or ""  # 如果找不到图片就留空或保持原路径
            results.append(data)

        return results,total_pages
    finally:
        db.close()

from sqlalchemy import update

def get_textbook_by_id(db: Session, textbook_id: int):
    textbook = db.query(Textbook).filter(Textbook.id == textbook_id).first()

    if not textbook:
        return None

    data = textbook.as_dict()
    # if data.get("cover"):
    #     cover_base64 = get_image_as_base64(data["cover"])
    #     data["cover"] = cover_base64 or data["cover"]  # 如果找不到图片保留原始路径

    return data


def update_oss_url(db: Session):
    # 查询所有 Textbook 记录
    textbooks = db.query(Textbook).all()

    for textbook in textbooks:
        # 拼接 oss_url
        new_oss_url = "http://whatteam1.oss-cn-beijing.aliyuncs.com/" + textbook.path
        # 更新 oss_url
        db.execute(
            update(Textbook).
            where(Textbook.id == textbook.id).
            values(oss_url=new_oss_url)
        )

    # 提交事务
    db.commit()

    return {"message": "OSS URLs updated successfully"}





